package com.sdc.DAOImpl;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import com.sdc.DAOFactory.DAOFactory;
import com.sdc.DAOInterface.UsuarioDAOInterface;
import com.sdc.bean.ProdutoBean;
import com.sdc.bean.UsuarioBean;

public class UsuarioDaoImpl extends DAOFactory implements UsuarioDAOInterface {

	private String query;
	private PreparedStatement ps;
	private static final Logger log = Logger.getLogger(UsuarioDaoImpl.class);
	private Connection con = null;
	private static final SimpleDateFormat sqlDateFormatter = new SimpleDateFormat(
			"yyyy-MM-dd");
	private static final SimpleDateFormat utilDateFormatter = new SimpleDateFormat(
			"dd/MM/yyyy");

	public static String sqlDateToutilDate(java.sql.Date sDate) {
		java.util.Date d = new Date(System.currentTimeMillis());
		try {

			if (sDate != null && !sDate.equals("")) {
				d = (java.util.Date) utilDateFormatter.parse(utilDateFormatter
						.format(sDate));
			} else {
				log
						.info("Erro ao converter data, valor obtido � nulo ou vazio");
			}
		} catch (ParseException e) {
			return utilDateFormatter.format(d);
			// e.printStackTrace();
		}
		return utilDateFormatter.format(d);
	}

	public static java.sql.Date stringToSqlDate(String uDate)
			throws ParseException {
		if (!uDate.equalsIgnoreCase("")) {

			java.util.Date utilDate = new java.util.Date(uDate);
			java.sql.Date sqlDate = new java.sql.Date(utilDate.getTime());
			return java.sql.Date.valueOf(sqlDateFormatter.format(sqlDate));
		}
		return null;
	}

	public UsuarioDaoImpl() {

		log.debug("DAO Usu�rio criado com sucesso");
	}

	public int inserirUsuario(UsuarioBean u) {
		try {
			query = "insert into Usuario (nome,cpf,dt_nasc,sexo,endereco,numero,bairro,cidade,estado,email,usuario,senha,id_tipo_usuario,dt_cadastro,id_situacao) "
					+ "values (?,?,?,?,?,?,?,?,?,?,?,? ,?,?,?)";

			con = abrirConexao();
			ps = con.prepareStatement(query);

			ps.setString(1, u.getNome());
			ps.setString(2, u.getCpf());
			ps.setDate(3, stringToSqlDate(u.getDt_nasc()));
			ps.setString(4, u.getSexo() + "");
			ps.setString(5, u.getEndereco());
			ps.setInt(6, u.getNumero());
			ps.setString(7, u.getBairro());
			ps.setString(8, u.getCidade());
			ps.setString(9, u.getEstado());
			ps.setString(10, u.getEmail());
			ps.setString(11, u.getUsuario());
			ps.setString(12, u.getSenha());
			ps.setInt(13, u.getId_tipo_usuario());
			ps.setDate(14, new java.sql.Date(new java.util.Date().getTime()));
			ps.setInt(15, u.getId_situacao());

			ps.execute();

			log.debug("Preparando query:" + ps);

			log.debug("Numero de linhas afetadas:");

		} catch (SQLException sqlException) {
			log.debug("Erro ao efetuar opera��o " + sqlException.getMessage());
			sqlException.printStackTrace();
		} catch (ParseException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				con.close();
				ps.close();
			} catch (Exception e) {
				e.printStackTrace();
				System.exit(1);
			}
		}
		return 0;
	}

	public int alterarUsuario(UsuarioBean u) {
		try {
			// query
			// =,
			// "update Usuario set nome=?,cpf=?,cpf,dt_nasc,sexo,endereco,numero,bairro,cidade,estado,email,usuario,senha,id_tipo_usuario,dt_cadastro) "
			// + "values (?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
			query = "update Usuario set nome=?,cpf=?,dt_nasc=?,sexo=?,endereco=?,numero=?,bairro=?,cidade=?,estado=?,email=?,usuario=?,senha=?,id_tipo_usuario=? where id_usuario=?";
			con = abrirConexao();
			ps = con.prepareStatement(query);

			ps.setString(1, u.getNome());
			ps.setString(2, u.getCpf());
			ps.setDate(3, stringToSqlDate(u.getDt_nasc()));
			ps.setString(4, u.getSexo());
			ps.setString(5, u.getEndereco());
			ps.setInt(6, u.getNumero());
			ps.setString(7, u.getBairro());
			ps.setString(8, u.getCidade());
			ps.setString(9, u.getEstado());
			ps.setString(10, u.getEmail());
			ps.setString(11, u.getUsuario());
			ps.setString(12, u.getSenha());
			ps.setInt(13, u.getId_tipo_usuario());
			ps.setInt(14, u.getId_usuario());

			log.debug("Preparando query: " + ps.toString());
			int lines = ps.executeUpdate();
			log.debug("Numero de linhas afetadas:" + lines);

			return lines;
		} catch (SQLException sqlException) {
			log.debug("Erro ao efetuar opera��o " + sqlException.getMessage());
			sqlException.printStackTrace();
		} catch (ParseException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				con.close();
				ps.close();
			} catch (Exception e) {
				e.printStackTrace();
				System.exit(1);
			}
		}
		return 0;
	}

	public int excluirUsuario(int id_usuario) {

		query = "update Usuario set id_situacao=3 where id_usuario=?";
		con = abrirConexao();
		try {
			ps = con.prepareStatement(query);
			ps.setInt(1, id_usuario);
			return ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			return 0;
		} finally {
			try {
				con.close();
				ps.close();
			} catch (Exception e) {
				e.printStackTrace();
				System.exit(1);
			}
		}
	}

	public int bloquearUsuario(int id_usuario) {

		query = "update Usuario set id_situacao=2 where id_usuario=?";
		con = abrirConexao();
		try {
			ps = con.prepareStatement(query);
			ps.setInt(1, id_usuario);
			return ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			return 0;
		} finally {
			try {
				con.close();
				ps.close();
			} catch (Exception e) {
				e.printStackTrace();
				System.exit(1);
			}
		}
	}

	public List<UsuarioBean> listarUsuarios() {
		query = "Select * from Usuario where id_situacao = 1 or id_situacao = 2";
		List<UsuarioBean> usuarios = new ArrayList<UsuarioBean>();
		ResultSet rs = null;
		con = abrirConexao();
		try {
			ps = con.prepareStatement(query);
			rs = ps.executeQuery();

			while (rs.next()) {
				UsuarioBean ub = new UsuarioBean();

				ub.setId_usuario(rs.getInt(1));
				ub.setNome(rs.getString(2));
				ub.setCpf(rs.getString(3));
				ub.setDt_nasc(sqlDateToutilDate(rs.getDate(4)));
				ub.setSexo(rs.getString(5));
				ub.setEndereco(rs.getString(6));
				ub.setNumero(rs.getInt(7));
				ub.setBairro(rs.getString(8));
				ub.setCidade(rs.getString(9));
				ub.setEstado(rs.getString(10));
				ub.setEmail(rs.getString(11));
				ub.setUsuario(rs.getString(12));
				ub.setSenha(rs.getString(13));
				ub.setId_tipo_usuario(rs.getInt(14));
				ub.setDt_cadastro(sqlDateToutilDate(rs.getDate(15)));
				ub.setId_situacao(rs.getInt(16));

				usuarios.add(ub);

			}
			return usuarios;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				con.close();
				ps.close();
			} catch (Exception e) {
				e.printStackTrace();
				System.exit(1);
			}
		}

		return usuarios;
	}

	public UsuarioBean autenticar(UsuarioBean u, String session_id) {
		query = "Select * from Usuario where usuario=? and senha=? and id_situacao=1 ";
		ResultSet rs = null;
		UsuarioBean ub = null;
		con = abrirConexao();
		try {
			ps = con.prepareStatement(query);

			ps.setString(1, u.getUsuario());
			ps.setString(2, u.getSenha());
			rs = ps.executeQuery();

			while (rs.next()) {
				ub = new UsuarioBean();
				ub.setId_usuario(rs.getInt(1));
				ub.setNome(rs.getString(2));
				ub.setCpf(rs.getString(3));
				ub.setDt_nasc(sqlDateToutilDate(rs.getDate(4)));
				ub.setSexo(rs.getString(5));
				ub.setEndereco(rs.getString(6));
				ub.setNumero(rs.getInt(7));
				ub.setBairro(rs.getString(8));
				ub.setCidade(rs.getString(9));
				ub.setEstado(rs.getString(10));
				ub.setEmail(rs.getString(11));
				ub.setUsuario(rs.getString(12));
				ub.setSenha(rs.getString(13));
				ub.setId_tipo_usuario(rs.getInt(14));
				ub.setDt_cadastro(sqlDateToutilDate(rs.getDate(15)));
				ub.setId_situacao(rs.getInt(16));
			}

			if (ub != null) {
				query = "Select top 1 format(login_tm, 'dd-mm-yyyy hh:mm:ss') from HistoricoSession where id_usuario=? order by login_tm desc";
				ps = con.prepareStatement(query);
				ps.setInt(1, ub.getId_usuario());
				rs = ps.executeQuery();

				while (rs.next()) {
					ub.setUltimo_login(rs.getString(1));
				}

				query = "insert into HistoricoSession (session_id,id_usuario, login_tm)  values(?,?,?)";
				ps = con.prepareStatement(query);
				ps.setString(1, session_id);
				ps.setInt(2, ub.getId_usuario());
				Timestamp ts = new Timestamp(System.currentTimeMillis());
				ps.setTimestamp(3, ts);

				ps.executeUpdate();

			}

			return ub;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				con.close();
				ps.close();
			} catch (Exception e) {
				e.printStackTrace();
				System.exit(1);
			}
		}
		return null;
	}

	public void deslogar(UsuarioBean u, String session_id, String ip) {
		con = abrirConexao();
		try {

			if (u != null) {
				query = "update HistoricoSession set id_usuario=?, logout_tm=?,ip_cliente=? where session_id=?";
				ps = con.prepareStatement(query);
				ps.setInt(1, u.getId_usuario());
				Timestamp ts = new Timestamp(System.currentTimeMillis());
				ps.setTimestamp(2, ts);
				ps.setString(3, ip);
				ps.setString(4, session_id);
				ps.executeUpdate();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				con.close();
				ps.close();
			} catch (Exception e) {
				e.printStackTrace();
				System.exit(1);
			}
		}
	}

	public List<UsuarioBean> filtrarUsuarios(String[] p) {
		query = "Select * from Usuario where nome like ? and usuario like ? and dt_cadastro like ? and id_situacao like ?";
		List<UsuarioBean> usuarios = new ArrayList<UsuarioBean>();
		ResultSet rs = null;
		con = abrirConexao();
		try {
			ps = con.prepareStatement(query);
			ps.setString(1, "%" + p[0] + "%");
			ps.setString(2, "%" + p[1] + "%");
			ps.setString(3, "%" + p[2] + "%");
			ps.setString(4, "%" + p[3] + "%");

			rs = ps.executeQuery();

			while (rs.next()) {
				UsuarioBean ub = new UsuarioBean();

				ub.setId_usuario(rs.getInt(1));
				ub.setNome(rs.getString(2));
				ub.setCpf(rs.getString(3));
				ub.setDt_nasc(sqlDateToutilDate(rs.getDate(4)));
				ub.setSexo(rs.getString(5));
				ub.setEndereco(rs.getString(6));
				ub.setNumero(rs.getInt(7));
				ub.setBairro(rs.getString(8));
				ub.setCidade(rs.getString(9));
				ub.setEstado(rs.getString(10));
				ub.setEmail(rs.getString(11));
				ub.setUsuario(rs.getString(12));
				ub.setSenha(rs.getString(13));
				ub.setId_tipo_usuario(rs.getInt(14));
				ub.setDt_cadastro(sqlDateToutilDate(rs.getDate(15)));
				ub.setId_situacao(rs.getInt(16));

				usuarios.add(ub);

			}
			return usuarios;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				con.close();
				ps.close();
			} catch (Exception e) {
				e.printStackTrace();
				System.exit(1);
			}
		}

		return usuarios;
	}

	public List<UsuarioBean> listarAcessosByUsuario() {
		query = "SELECT distinct(hs.id_usuario), u.nome, u.usuario, u.id_situacao, u.id_tipo_usuario, "
				+ "(select count(hsAux.id_usuario) "
				+ " from HistoricoSession AS hsAux "
				+ " where hsAux.id_usuario =  hs.id_usuario) as qtdeAcesso, "
				+ "(select format(max(hsAux2.login_tm), 'dd-mm-yyyy hh:mm:ss') "
				+ " from HistoricoSession hsAux2 "
				+ " where hsAux2.id_usuario =  hs.id_usuario) as ultAcesso "
				+ "FROM HistoricoSession AS hs INNER JOIN Usuario AS u ON hs.id_usuario = u.id_usuario "
				+ "ORDER BY u.nome;";
		List<UsuarioBean> acessos = new ArrayList<UsuarioBean>();
		ResultSet rs = null;
		con = abrirConexao();
		try {
			ps = con.prepareStatement(query);
			rs = ps.executeQuery();

			while (rs.next()) {
				UsuarioBean ac = new UsuarioBean();

				ac.setNome(rs.getString(2));
				ac.setUsuario(rs.getString(3));
				ac.setId_situacao(rs.getInt(4));
				ac.setId_tipo_usuario(rs.getInt(5));
				ac.setQtdeAcessos(rs.getInt(6));
				ac.setUltimo_login(rs.getString(7));

				acessos.add(ac);

			}
			return acessos;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				con.close();
				ps.close();
			} catch (Exception e) {
				e.printStackTrace();
				System.exit(1);
			}
		}

		return acessos;
	}

	public List<UsuarioBean> listarAcessosByUsuario(String[] p) {
		query = "SELECT distinct(hs.id_usuario), "
		            +"u.nome, "
		            +"u.usuario, " 
		            +"u.id_situacao, " 
		            +"u.id_tipo_usuario, "
		            +"(select count(hsAux.id_usuario) from HistoricoSession AS hsAux where hsAux.id_usuario =  hs.id_usuario and format(hsAux.login_tm, 'dd-mm-yyyy') between ? and ? ) AS qtdeAcesso, "
		            +"(select format(max(hsAux2.login_tm), 'dd-mm-yyyy') from HistoricoSession AS hsAux2 where hsAux2.id_usuario = hs.id_usuario and format(hsAux2.login_tm, 'dd-mm-yyyy') between ? and ?) AS Ultimo_login "
	            +"FROM HistoricoSession AS hs "
				+"INNER JOIN Usuario AS u ON hs.id_usuario = u.id_usuario "
				+"WHERE format(hs.login_tm, 'dd-mm-yyyy') between ? and ? order by u.nome";

		List<UsuarioBean> acessos = new ArrayList<UsuarioBean>();
		ResultSet rs = null;
		con = abrirConexao();
		try {
			
			ps = con.prepareStatement(query);
			ps.setString(1, p[0]);
			ps.setString(2, p[1]);
			ps.setString(3, p[0]);
			ps.setString(4, p[1]);
			ps.setString(5, p[0]);
			ps.setString(6, p[1]);
			rs = ps.executeQuery();

			while (rs.next()) {
				UsuarioBean ac = new UsuarioBean();

				ac.setNome(rs.getString(2));
				ac.setUsuario(rs.getString(3));
				ac.setId_situacao(rs.getInt(4));
				ac.setId_tipo_usuario(rs.getInt(5));
				ac.setQtdeAcessos(rs.getInt(6));
				ac.setUltimo_login(rs.getString(7));

				acessos.add(ac);

			}
			return acessos;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				con.close();
				ps.close();
			} catch (Exception e) {
				e.printStackTrace();
				System.exit(1);
			}
		}

		return acessos;
	}
}
